package com.leejw.tradeblogsystem.common;

import com.leejw.tradeblogsystem.model.Record;
import com.leejw.tradeblogsystem.model.Shares;
import com.leejw.tradeblogsystem.model.ZTShares;
import org.apache.poi.hssf.usermodel.*;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.springframework.web.multipart.MultipartFile;


import java.text.DecimalFormat;
import java.text.SimpleDateFormat;
import java.time.LocalDate;
import java.time.LocalDateTime;
import java.time.format.DateTimeFormatter;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;

public class ExcelUtil {

    private final static String excel2003L = ".xls";    //2003- 版本的excel
    private final static String excel2007U = ".xlsx";   //2007+ 版本的excel

    /**
     * 实现批量导入
     *
     * @param myFile
     * @param obj
     * @return
     */
    public static List importExcel(MultipartFile myFile, Object obj) throws Exception {
        //        1、用HSSFWorkbook打开或者创建“Excel文件对象”
        //
        //        2、用HSSFWorkbook对象返回或者创建Sheet对象
        //
        //        3、用Sheet对象返回行对象，用行对象得到Cell对象
        //
        //        4、对Cell对象读写。
        //获得文件名
        Workbook workbook = null;
        String fileName = myFile.getOriginalFilename();
        if (fileName.endsWith(excel2003L)) {
            //2003
            workbook = new HSSFWorkbook(myFile.getInputStream());
        } else if (fileName.endsWith(excel2007U)) {
            //2007
            workbook = new XSSFWorkbook(myFile.getInputStream());
        } else {
            throw new Exception("文件不是Excel文件");
        }

        Sheet sheet = workbook.getSheet("Sheet1");
        int rows = sheet.getLastRowNum();// 指的行数，一共有多少行+
        if (rows == 0) {
            throw new Exception("请填写数据");
        }
        List list = new ArrayList();
        for (int i = 1; i <= rows + 1; i++) {
            // 读取左上端单元格
            Row row = sheet.getRow(i);
            // 行不为空
            if (row != null) {
                if (obj instanceof ZTShares) {
                    ZTShares shares = new ZTShares();
                    String shareName = getCellValue(row.getCell(0));
                    shares.setShareName(shareName);

                    String shareCode = getCellValue(row.getCell(1));
                    shares.setShareCode(shareCode);

                    String price = getCellValue(row.getCell(2));
                    shares.setPrice(Double.parseDouble(price));

                    String turnover = getCellValue(row.getCell(3));
                    shares.setTurnover(Double.parseDouble(turnover));

                    String industryId = getCellValue(row.getCell(4));
                    shares.setIndustryId(industryId);

                    String Attribute1 = getCellValue(row.getCell(5));
                    shares.setAttribute1(Attribute1);
                    shares.setBoard(1);
                    list.add(shares);
                } else if (obj instanceof Shares) {
                    Shares shares = new Shares();
                    String shareCode = getCellValue(row.getCell(0));
                    shares.setShareCode(shareCode);

                    String shareName = getCellValue(row.getCell(1));
                    shares.setShareName(shareName);

                    String price = getCellValue(row.getCell(2));
                    shares.setPrice(Double.parseDouble(price));

                    String scope = getCellValue(row.getCell(3));
                    shares.setScope(scope);

                    String TTMPE = getCellValue(row.getCell(4));
                    shares.setTTMPE(Double.parseDouble(TTMPE));

                    String netProfit = getCellValue(row.getCell(5));
                    shares.setNetProfit(netProfit);

                    String bookRatio = getCellValue(row.getCell(6));
                    shares.setBookRatio(Double.parseDouble(bookRatio));

                    String perShare = getCellValue(row.getCell(7));
                    shares.setPerShare(Double.parseDouble(perShare));

                    String industryId = getCellValue(row.getCell(8));
                    shares.setIndustryId(industryId);

                    String bkId = getCellValue(row.getCell(9));
                    shares.setBkId(bkId);

                    String fiveIncrease = getCellValue(row.getCell(10));
                    shares.setFiveIncrease(Double.parseDouble(fiveIncrease));

                    String tenIncrease = getCellValue(row.getCell(11));
                    shares.setTenIncrease(Double.parseDouble(tenIncrease));

                    String twentyIncrease = getCellValue(row.getCell(12));
                    shares.setTwentyIncrease(Double.parseDouble(twentyIncrease));

                    String yearIncrease = getCellValue(row.getCell(13));
                    shares.setYearIncrease(Double.parseDouble(yearIncrease));

                    list.add(shares);
                } else if (obj instanceof Record) {
                    Record record = new Record();
                    String shareCode = getCellValue(row.getCell(0));
                    record.setShareCode(shareCode);

                    String shareName = getCellValue(row.getCell(1));
                    record.setShareName(shareName);

                    String buyPrice = getCellValue(row.getCell(2));
                    record.setBuyPrice(buyPrice);

                    String count = getCellValue(row.getCell(3));
                    record.setCount(count);

                    String total = getCellValue(row.getCell(4));
                    record.setTotal(total);

                    String sellPrice = getCellValue(row.getCell(5));
                    record.setSellPrice(sellPrice);

                    String tax = getCellValue(row.getCell(6));
                    record.setTax(tax);

                    String salary = getCellValue(row.getCell(7));
                    record.setSalary(salary);

                    String recordContent = getCellValue(row.getCell(8));
                    record.setRecordContent(recordContent);
                    list.add(record);
                }


            }
        }
        return list;
    }

    /**
     * 实现批量导出（全量数据）
     *
     * @param response
     * @param obj
     * @return
     */
//    public static void exportExcel(HttpServletResponse response, List list, Object obj) throws Exception {
//        // 第一步，创建一个webbook，对应一个Excel文件
//        HSSFWorkbook wb = new HSSFWorkbook();
//        // 第二步，在webbook中添加一个sheet,对应Excel文件中的sheet
//        HSSFSheet sheet = wb.createSheet("Sheet1");
//        // 第三步，在sheet中添加表头第0行,注意老版本poi对Excel的行数列数有限制short
//        HSSFRow row = sheet.createRow(0);
//        // 第四步，创建单元格，并设置值表头 设置表头居中
//        HSSFCellStyle style = wb.createCellStyle();
//        style.setAlignment(HSSFCellStyle.ALIGN_CENTER); // 创建一个居中格式
//        if(obj instanceof Product){
//            HSSFCell cell = row.createCell(0);
//            cell.setCellValue("产品代码");
//            cell.setCellStyle(style);
//            cell = row.createCell(1);
//            cell.setCellValue("产品名字");
//            cell.setCellStyle(style);
//            cell = row.createCell(2);
//            cell.setCellValue("城市名字");
//            cell.setCellStyle(style);
//            cell = row.createCell(3);
//            cell.setCellValue("时间");
//            cell = row.createCell(4);
//            cell.setCellValue("产品价格");
//            cell = row.createCell(5);
//            cell.setCellValue("产品描述");
//            cell.setCellStyle(style);
//
//            for (int i = 0; i < list.size(); i++){
//                row = sheet.createRow(i + 1);
//                Product product = (Product) list.get(i);
//                // 第四步，创建单元格，并设置值
//                row.createCell(0).setCellValue(product.getProductNum());
//                row.createCell(1).setCellValue(product.getProductName());
//                row.createCell(2).setCellValue(product.getCityName());
//                cell = row.createCell(3);
//                cell.setCellValue(new SimpleDateFormat("yyyy-MM-dd").format(product.getDepartureTime()));
//                row.createCell(4).setCellValue(product.getProductPrice());
//                row.createCell(5).setCellValue(product.getProductDesc());
//
//            }
//        }
//        //第六步,输出Excel文件
//        OutputStream output=response.getOutputStream();
//        response.reset();
//        SimpleDateFormat df = new SimpleDateFormat("yyyy-MM-dd");//设置日期格式
//        String fileName = df.format(new Date()) ;// new Date()为获取当前系统时间
//        response.setHeader("Content-disposition", "attachment; filename="+fileName+".xls");
//        response.setContentType("application/msexcel");
//        wb.write(output);
//        output.close();
//    }

    /**
     * 获得Cell内容
     *
     * @param cell
     * @return
     */
    private static String getCellValue(Cell cell) {
        String value = "";
        if (cell != null) {
            // 以下是判断数据的类型
            switch (cell.getCellType()) {
                case HSSFCell.CELL_TYPE_NUMERIC: // 数字
                    if (HSSFDateUtil.isCellDateFormatted(cell)) {
                        Date date = cell.getDateCellValue();
                        if (date != null) {
                            value = new SimpleDateFormat("yyyy-MM-dd").format(date);
                        } else {
                            value = "";
                        }
                    }else{
                        value = cell.getNumericCellValue() + "";
                        if (value.contains("E")) {
                            double cellValue = cell.getNumericCellValue();
                            value = scientificNotationString(cellValue);
                        }
                    }
                    break;
                case HSSFCell.CELL_TYPE_STRING: // 字符串
                    value = cell.getStringCellValue();
                    break;
                case HSSFCell.CELL_TYPE_BOOLEAN: // Boolean
                    value = cell.getBooleanCellValue() + "";
                    break;
                case HSSFCell.CELL_TYPE_FORMULA: // 公式
                    value = cell.getCellFormula() + "";
                    break;
                case HSSFCell.CELL_TYPE_BLANK: // 空值
                    value = "";
                    break;
                case HSSFCell.CELL_TYPE_ERROR: // 故障
                    value = "非法字符";
                    break;
                default:
                    value = "未知类型";
                    break;
            }
        }
        return value.trim();
    }

    public static String scientificNotationString(Double val) {
        String res = new DecimalFormat(Const.FMT_DOUBLE).format(val);
        if (val >= 1) {
            String substring = res.substring(0, res.length() - 3);
            double temp = Double.parseDouble(substring);
            res = temp * 100 + "亿";
        }
        return res;
    }
}
